package com.ywjh.inter.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import com.mysql.jdbc.Statement;
import com.ywjh.inter.entity.BloodGlucoseEntity;

public class BloodGlucoseDAO {

	public int insert(Connection conn, BloodGlucoseEntity bg) throws SQLException {
		int id = -1;
		PreparedStatement ps = conn.prepareStatement(
				"INSERT INTO blood_glucose (user_id, data, add_time, check_time,status,plan_id,memo,data_source) VALUES (?,?,?,?,?,?,?,?)",Statement.RETURN_GENERATED_KEYS);
		ps.setInt(1, bg.getUser_id());
		ps.setFloat(2, bg.getData());
		ps.setTimestamp(3, bg.getAdd_time());
		ps.setTimestamp(4, bg.getCheck_time());
		ps.setInt(5, bg.getStatus());
		ps.setInt(6, bg.getPlan_id());
		ps.setString(7, bg.getMemo());
		ps.setString(8, bg.getData_source());
		if (ps.executeUpdate() > 0) {
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				id = rs.getInt(1);
			}
		}
		return id;
	}

	public boolean update(Connection conn, BloodGlucoseEntity bg) throws SQLException {
		boolean b = false;
		PreparedStatement ps = conn.prepareStatement(
				"update blood_glucose set user_id=?, data=?, add_time=?, status=?,check_time=?,plan_id=?,memo=?,data_source=? where id = ?");
		ps.setInt(1, bg.getUser_id());
		ps.setFloat(2, bg.getData());
		ps.setTimestamp(3, bg.getAdd_time());
		ps.setInt(4, bg.getStatus());
		ps.setTimestamp(5, bg.getCheck_time());
		ps.setInt(6, bg.getPlan_id());
		ps.setString(7, bg.getMemo());
		ps.setString(8, bg.getData_source());
		ps.setInt(9, bg.getId());

		if(ps.executeUpdate()>0) {
			b=true;
		}
		return b;
	}

	public BloodGlucoseEntity findById(Connection conn,int id) throws SQLException {
		BloodGlucoseEntity e=null;
		PreparedStatement ps=conn.prepareStatement("select * from blood_glucose where id = ? ");
		ps.setInt(1, id);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			e=fill(rs);
		}
		return e;
	}

	private BloodGlucoseEntity fill(ResultSet rs) throws SQLException {
		BloodGlucoseEntity e=new BloodGlucoseEntity();
		e.setId(rs.getInt("id"));
		e.setAdd_time(rs.getTimestamp("add_time"));
		e.setData(rs.getFloat("data"));
		e.setStatus(rs.getInt("status"));
		e.setUser_id(rs.getInt("user_id"));
		e.setCheck_time(rs.getTimestamp("check_time"));
		e.setPlan_id(rs.getInt("plan_id"));
		e.setMemo(rs.getString("memo"));
		e.setData_source(rs.getString("data_source"));
		return e;
	}

	public List<BloodGlucoseEntity> query(Connection conn, int userId, String time, int planId) throws SQLException {
		List<BloodGlucoseEntity> list=new LinkedList<>();
		PreparedStatement ps=conn.prepareStatement("select * from blood_glucose where date_format(check_time,'%y%m%d') = date_format(?,'%y%m%d') and user_id=? and plan_id=? and status=1 order by check_time desc");
		ps.setString(1, time);
		ps.setInt(2, userId);
		ps.setInt(3, planId);
		ResultSet rs=ps.executeQuery();

		while(rs.next()) {
			list.add(fill(rs));
		}
		return list;
	}

	public List<BloodGlucoseEntity> query(Connection conn, int userId, String startTime,String endTime) throws SQLException {
		List<BloodGlucoseEntity> list=new LinkedList<>();
		PreparedStatement ps=conn.prepareStatement("select * from blood_glucose where date_format(check_time,'%y%m%d') >= date_format(?,'%y%m%d') and date_format(check_time,'%y%m%d') <= date_format(?,'%y%m%d') and user_id=? and status=1");
		ps.setString(1, startTime);
		ps.setString(2, endTime);
		ps.setInt(3, userId);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			list.add(fill(rs));
		}
		return list;
	}

	public List<BloodGlucoseEntity> list(Connection conn,int user_id, int size) throws SQLException {
		List<BloodGlucoseEntity> list=new LinkedList<>();
		PreparedStatement ps=conn.prepareStatement("select * from blood_glucose where user_id=? and status=1 order by check_time desc limit ?");
		ps.setInt(1, user_id);
		ps.setInt(2, size);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			list.add(fill(rs));
		}
		return list;
	}

	public List<BloodGlucoseEntity> query(Connection conn, String users, String startTime, String endTime) throws SQLException {
		List<BloodGlucoseEntity> list=new LinkedList<>();
		if(users==null||"".equals(users)) {
			return list;
		}
		PreparedStatement ps=conn.prepareStatement("select * from blood_glucose where date_format(check_time,'%y%m%d') >= date_format(?,'%y%m%d') and date_format(check_time,'%y%m%d') <= date_format(?,'%y%m%d') and user_id in ("+users+") and status=1");
		ps.setString(1, startTime);
		ps.setString(2, endTime);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			list.add(fill(rs));
		}
		System.out.println("list=="+list.size());
		return list;
	}

	public List<BloodGlucoseEntity> query(Connection conn, String users) throws SQLException {

		List<BloodGlucoseEntity> list=new LinkedList<>();
		if(users==null||"".equals(users)) {
			return list;
		}
		PreparedStatement ps=conn.prepareStatement("select * from blood_glucose where  user_id in ("+users+") and status=1");
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			list.add(fill(rs));
		}
		return list;
	}

}
